package com.fsnbigdata.repository;
import com.fsnbigdata.domain.Ecosystem;
import com.fsnbigdata.domain.dto.NamingPair;
import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.Query;
import org.springframework.roo.addon.layers.repository.jpa.annotations.RooJpaRepository;
import io.springlets.data.jpa.repository.DetachableJpaRepository;
import org.springframework.transaction.annotation.Transactional;

/**
 * = EcosystemRepository
 *
 * TODO Auto-generated class documentation
 *
 */
@RooJpaRepository(entity = Ecosystem.class)
@Transactional(readOnly = true)
public interface EcosystemRepository extends DetachableJpaRepository<Ecosystem, Long>, EcosystemRepositoryCustom {

    List<NamingPair> findAllByInitial(String initial);

    long countByCreator(String creator);

    List<Ecosystem> findByType(Integer type);

    Page<Ecosystem> findAll(Specification<Ecosystem> spec, Pageable pageable);

    @Query(value = "select count(*) from ecosystem where \n" +
            "if(?1 !='',name like concat('%',?1,'%'),1=1) \n" +
            "AND if(?2 !='',administrative_location like concat('%',?2,'%'),1=1) \n" +
            "AND if(?3 !='',current_level like concat('%',?3,'%'),1=1) \n" +
            "AND if(?4 !='',create_date like concat('%',?4,'%'),1=1) \n" +
            "AND if(?5 !='',administrative_department like concat('%',?5,'%'),1=1) \n" +
            "AND if(?6 !='',type =?6,1=1) ",nativeQuery = true)
    Long queryCountByKey(String name,String administrativeLocation,String currentLevel,
                         String createDate, String administrativeDepartment,String type);

    @Query(value = "select id,name,approver,administrative_department,current_level,type,category from ecosystem where if(?1 !='',name like concat('%',?1,'%'),1=1) AND if(?2 !='',administrative_location like concat('%',?2,'%'),1=1) AND if(?3 !='',current_level like concat('%',?3,'%'),1=1) AND if(?4 !='',create_date like concat('%',?4,'%'),1=1) AND if(?5 !='',administrative_department like concat('%',?5,'%'),1=1) AND if(?6 !='',type =?6,1=1) ORDER BY ?#{#pageable}",
            countQuery = "select count(*) from ecosystem where \n" +
                    "if(?1 !='',name like concat('%',?1,'%'),1=1) \n" +
                    "AND if(?2 !='',administrative_location like concat('%',?2,'%'),1=1) \n" +
                    "AND if(?3 !='',current_level like concat('%',?3,'%'),1=1) \n" +
                    "AND if(?4 !='',create_date like concat('%',?4,'%'),1=1) \n" +
                    "AND if(?5 !='',administrative_department like concat('%',?5,'%'),1=1) \n" +
                    "AND if(?6 !='',type =?6,1=1) ",nativeQuery = true)
    List<Object> queryAllByKey(String name,String administrativeLocation,String currentLevel,
                               String createDate, String administrativeDepartment,String type,Pageable pageable);

    @Query(value = "select * from ecosystem",nativeQuery = true)
    List<Object> getCount();
}
